/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package merlion.frm.ccm.session;

import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import merlion.crm.slcm.entity.Customer;
import merlion.frm.ccm.entity.CreditAccount;
import java.util.List;
import javax.ejb.EJB;
import javax.ejb.Schedule;
import javax.jms.Connection;
import javax.jms.ConnectionFactory;
import javax.jms.Destination;
import javax.jms.JMSException;
import javax.jms.MapMessage;
import javax.jms.MessageProducer;
import javax.jms.Session;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import merlion.common.entity.Announcement;
import merlion.common.entity.SecurityRole;
import merlion.common.entity.Staff;
import merlion.common.util.Consts;
import merlion.crm.slcm.entity.ContactRecord;
import merlion.crm.slcm.session.SLCSessionBeanLocal;
import merlion.crm.sopm.entity.SalesOrder;
import merlion.crm.sopm.session.SOPSessionBeanLocal;
import merlion.frm.bpm.entity.Invoice;
import merlion.frm.ccm.entity.CreditGroup;
import merlion.scm.icm.entity.ProductInventoryRecord;
import merlion.scm.rmpm.entity.RawMaterialInventoryRecord;

/**
 *
 * @author Yuwei
 */
@Stateless
public class CCSessionBean implements CCSessionBeanLocal, CCSessionBeanRemote {

    @PersistenceContext
    private EntityManager entityManager;
    @EJB
    private SLCSessionBeanLocal sLCSessionBean;
    @EJB
    private SOPSessionBeanLocal sOPSessionBean;
    private static final String EVERY = "*";
    private static final String ZERO = "0";

    @Schedule(dayOfMonth = EVERY, month = EVERY, year = EVERY, second = ZERO,
    minute = ZERO, hour = EVERY)
    @Override
    public void checkUnpaidInvoices() {
        System.out.println("@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@check invoice timer test!");

        Query query = entityManager.createQuery("SELECT i FROM Invoice i WHERE i.paid =:p1");
        query.setParameter("p1", Boolean.FALSE);
        List<Invoice> unpaidInvoices = query.getResultList();
        Invoice inv;
        Timestamp invoiceDueDateTime;
        Timestamp currentTime;
        Timestamp alertTime;
        long oneDay = (long) (1 * 24 * 60 * 60 * 1000);
        for (int i = 0; i < unpaidInvoices.size(); i++) {
            inv = unpaidInvoices.get(i);
            currentTime = new Timestamp(System.currentTimeMillis());
            invoiceDueDateTime = inv.getInvoiceDueDate();
            //Alert customer 5 days before invoice due
            alertTime = new Timestamp(new Date().getTime());
            alertTime.setTime(invoiceDueDateTime.getTime() - 5 * oneDay);
            System.out.println("CC Session Bean: alert Time - " + alertTime);
            System.out.println("CC Session Bean: current Time - " + currentTime);
            if (currentTime.after(alertTime) && currentTime.before(invoiceDueDateTime)) {
                //Email notification to customers
                System.out.println("@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@Invoice due soon: " + inv.getCustomer().getCompanyName());
                sendInvoiceDue(inv);
            } else if (currentTime.after(invoiceDueDateTime)) {
                //Mark as Default
                System.out.println("CC Session Bean: going to mark it as default");
                if (inv.getCustomer().getCreditAccount().isIsDefault() == false) {
                    System.out.println("@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@Customer defaulted: customer: " + inv.getCustomer().getCompanyName());
                    inv.getCustomer().getCreditAccount().setIsDefault(true);
                    //Block the customer's all processing salesOrders
                    sOPSessionBean.cancelSalesOrder(inv.getDeliveryOrder().getSalesOrder().getSalesOrderIdStr());
                    //Email notification to customers
                    sendInvoiceDefault(inv);
                }

            }
        }

        //Check Product inventory level
        Query query1 = entityManager.createQuery("SELECT p FROM ProductInventoryRecord p");
        List<ProductInventoryRecord> productInventories = query1.getResultList();
        for (int i = 0; i < productInventories.size(); i++) {
            if (productInventories.get(i).getBalance() < productInventories.get(i).getOptimalStock()) {
                Announcement newAnnoucement = new Announcement();
                newAnnoucement.setAnnounceDate(new Timestamp(new Date().getTime()));
                Query query2 = entityManager.createQuery("SELECT sr FROM SecurityRole sr WHERE sr.roleName=:p1 OR sr.roleName=:p2");
                query2.setParameter("p2", Consts.MRP_VP_ROLE);
                query2.setParameter("p1", Consts.MRP_MANAGER_ROLE);
                List<SecurityRole> auds = query2.getResultList();

                newAnnoucement.setAudience(auds);
                newAnnoucement.setContent("Product Inventory Balance below Optimal Stock!");
                Query query3 = entityManager.createQuery("SELECT s FROM Staff s WHERE s.systemUserAccount.username=:p1");
                query3.setParameter("p1", "zhouhong");
                Staff s = (Staff) query3.getSingleResult();
                newAnnoucement.setPosterId(s.getId());
                newAnnoucement.setTitle("Product Inventory Balance below Optimal Stock");
                entityManager.merge(newAnnoucement);
            }
        }
        //Check Raw Material inventory level
        Query query4 = entityManager.createQuery("SELECT r FROM RawMaterialInventoryRecord r");
        List<RawMaterialInventoryRecord> rawMaterialInventories = query4.getResultList();
        for (int i = 0; i < rawMaterialInventories.size(); i++) {
            if (rawMaterialInventories.get(i).getBalance() < rawMaterialInventories.get(i).getOptimalStock()) {
                Announcement newAnnoucement = new Announcement();
                newAnnoucement.setAnnounceDate(new Timestamp(new Date().getTime()));
                Query query5 = entityManager.createQuery("SELECT sr FROM SecurityRole sr WHERE sr.roleName=:p1 OR sr.roleName=:p2");
                query5.setParameter("p2", Consts.MRP_VP_ROLE);
                query5.setParameter("p1", Consts.MRP_MANAGER_ROLE);
                List<SecurityRole> auds = query5.getResultList();

                newAnnoucement.setAudience(auds);
                newAnnoucement.setContent("Raw Material Inventory Balance below Optimal Stock!");
                Query query6 = entityManager.createQuery("SELECT s FROM Staff s WHERE s.systemUserAccount.username=:p1");
                query6.setParameter("p1", "zhouhong");
                Staff s = (Staff) query6.getSingleResult();
                newAnnoucement.setPosterId(s.getId());
                newAnnoucement.setTitle("Raw Material Inventory Balance below Optimal Stock");
                entityManager.merge(newAnnoucement);

            }
        }
    }

        @Override
        public boolean creditCheck(SalesOrder so) {
            System.out.println("pass so successfullly");
        boolean pass = Boolean.FALSE;
            double creditBalance = so.getCustomer().getCreditAccount().getCreditBalance();
            System.out.println("get credit balance successfully");
            double soTotal = so.getTotal();
            if (soTotal < creditBalance) {
                //can buy on credit
                pass = Boolean.TRUE;
            }
            return pass;
        }

        @Override
        public CreditAccount getCustomerCreditAccount        (Customer customer 
            ) {

        Query query = entityManager.createQuery("SELECT c FROM Customer c WHERE c.id = :p1");
            query.setParameter("p1", customer.getId());

            List<Customer> list = query.getResultList();
            Customer c = list.get(0);
            CreditAccount ca = c.getCreditAccount();
            if (ca != null) {
                return ca;
            }
            return null;
        }

        @Override
        public List<CreditGroup> getAllCreditGroup
        
            () {
        Query query = entityManager.createQuery("SELECT g FROM CreditGroup g");
            return query.getResultList();
        }

        @Override
        public CreditGroup createCreditGroup
        (CreditGroup cg
        
            ) {

        try {
                //check credit group name not exist
                Collection<CreditGroup> list = entityManager.createQuery("SELECT cg FROM CreditGroup cg WHERE cg.creditGroupTitle LIKE '" + cg.getCreditGroupTitle() + "'").getResultList();
                if (!list.isEmpty()) {
                    return null;
                }

                entityManager.merge(cg);
            } catch (Exception e) {
                return null;
            }
            return cg;
        }

        @Override
        public CreditGroup getCreditGroupById
        (Long id
        
            ) {

        Query query = entityManager.createQuery("SELECT c FROM CreditGroup c WHERE c.id =:p1");
            query.setParameter("p1", id);
            if (query.getResultList() == null || query.getResultList().isEmpty()) {
                return null;
            }
            CreditGroup cg = (CreditGroup) query.getResultList().get(0);
            return cg;
        }

        @Override
        public Boolean checkExistCreditGroupByTitle
        (String title
        
            ) {

        Query query = entityManager.createQuery("SELECT c FROM CreditGroup c WHERE c.creditGroupTitle LIKE :p1");
            query.setParameter("p1", title);
            Collection<CreditGroup> cg = query.getResultList();
            if (cg.isEmpty()) {
                return false;
            }
            return true;
        }

        @Override
        public CreditGroup updateCreditGroup
        (CreditGroup cg
        
            ) {
        return entityManager.merge(cg);
        }

        @Override
        public CreditAccount createCreditAccount
        (CreditAccount ca
        
            ) {
        try {
                Customer customer = sLCSessionBean.getCustomerByClientId(ca.getCustomer().getClientId());
                ca.setCustomer(customer);
                customer.setCreditAccount(ca);
                //Set credit account Balance
                ca.setCreditUsed((double) 0);
                if (customer.getCategory().equals("DirectSale")) {
                    ca.setCreditBalance(ca.getCreditGroup().getCreditLimitForDs());
                } else {
                    ca.setCreditBalance(ca.getCreditGroup().getCreditLimitForWs());
                }
                entityManager.merge(ca);
                return ca;
            } catch (Exception ex) {
                return null;
            }
        }

        @Override
        public boolean removeCreditGroupById
        (Long id
        
            ) {
        boolean removed = Boolean.FALSE;
            CreditGroup cg = getCreditGroupById(id);
            ArrayList<CreditAccount> linkedAccounts = new ArrayList<CreditAccount>(cg.getCreditAccounts());
            if (linkedAccounts.isEmpty()) {//no linked accounts
                try {
                    entityManager.remove(cg);
                    removed = Boolean.TRUE;
                } catch (Exception e) {
                    //remove failed
                }
            }
            return removed;
        }

        @Override
        public List<Customer> getCustomersNoCreditAccount
        
            () {
        return entityManager.createQuery("SELECT c FROM Customer c WHERE c.creditAccount IS NULL").getResultList();
        }

        @Override
        public List<Customer> getCustomersWithCreditAccount
        
            () {
        return entityManager.createQuery("SELECT c FROM Customer c WHERE c.creditAccount IS NOT NULL").getResultList();
        }

    

    private void sendInvoiceDue(Invoice invoice) {
        // send the email to MDB
        try {
            Context c = new InitialContext();
            ConnectionFactory cf = (ConnectionFactory) c.lookup("jms/queueSendEmailFactory");
            Connection conn = null;
            Session s = null;

            try {
                conn = cf.createConnection();
                s = conn.createSession(false, s.AUTO_ACKNOWLEDGE);
                Destination destination = (Destination) c.lookup("jms/queueSendEmail");
                MessageProducer mp = s.createProducer(destination);
                MapMessage mmsg = s.createMapMessage();
                mmsg.setString("token", null);
                mmsg.setString("username", invoice.getCustomer().getCompanyName());
                mmsg.setString("password", Consts.sdfDmy.format(invoice.getInvoiceDueDate()));
                mmsg.setString("email", new ArrayList<ContactRecord>(invoice.getCustomer().getContactRecords()).get(0).getEmail());
                mmsg.setString("type", Consts.EMAIL_INVOICE_DUE);
                mmsg.setString("invoiceId", invoice.getInvoiceId());
                mp.send(mmsg);
            } catch (JMSException jmsEx) {
                jmsEx.printStackTrace();
            } finally {
                if (s != null) {
                    s.close();
                }
                if (conn != null) {
                    conn.close();
                }
            }
        } catch (NamingException namingEx) {
            namingEx.printStackTrace();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }

    private void sendInvoiceDefault(Invoice invoice) {
        System.out.println("Send Invoice Default method called.");
        // send the email to MDB
        try {
            Context c = new InitialContext();
            ConnectionFactory cf = (ConnectionFactory) c.lookup("jms/queueSendEmailFactory");
            Connection conn = null;
            Session s = null;

            try {
                conn = cf.createConnection();
                s = conn.createSession(false, s.AUTO_ACKNOWLEDGE);
                Destination destination = (Destination) c.lookup("jms/queueSendEmail");
                MessageProducer mp = s.createProducer(destination);
                MapMessage mmsg = s.createMapMessage();
                mmsg.setString("token", null);
                mmsg.setString("username", invoice.getCustomer().getCompanyName());
                mmsg.setString("password", null);
                mmsg.setString("email", new ArrayList<ContactRecord>(invoice.getCustomer().getContactRecords()).get(0).getEmail());
                mmsg.setString("type", Consts.EMAIL_CUSTOMER_DEFAULT);
                mmsg.setString("invoiceId", invoice.getInvoiceId());
                mp.send(mmsg);
            } catch (JMSException jmsEx) {
                jmsEx.printStackTrace();
            } finally {
                if (s != null) {
                    s.close();
                }
                if (conn != null) {
                    conn.close();
                }
            }
        } catch (NamingException namingEx) {
            namingEx.printStackTrace();
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
    
    @Override
    public CreditGroup getPreviousCreditGroupDs(CreditGroup cg){
        
        double upgradeLimit = cg.getUpgradeLimitForDs();
        Query q = entityManager.createQuery("SELECT c FROM CreditGroup c WHERE c.upgradeLimitForDs <:p1 ORDER BY c.upgradeLimitForDs DESC");
        q.setParameter("p1", upgradeLimit);
        ArrayList<CreditGroup> list = new ArrayList<CreditGroup>(q.getResultList());
        if(list.isEmpty()){
           return null; 
        }
        CreditGroup prev = list.get(0);
        System.out.println("Prev DS: " + prev.getCreditGroupTitle() + " " + prev.getCreditLimitForDs());
        
        return prev;
    }
    
    @Override
    public CreditGroup getNextCreditGroupDs(CreditGroup cg){
        
        double upgradeLimit = cg.getUpgradeLimitForDs();
        Query q = entityManager.createQuery("SELECT c FROM CreditGroup c WHERE c.upgradeLimitForDs >:p1 ORDER BY c.upgradeLimitForDs ASC");
        q.setParameter("p1", upgradeLimit);
        ArrayList<CreditGroup> list = new ArrayList<CreditGroup>(q.getResultList());
        if(list.isEmpty()){
           return null; 
        }
        CreditGroup next = list.get(0);
        System.out.println("Next DS: " + next.getCreditGroupTitle() + " " + next.getCreditLimitForDs());
        
        return next;
    }
    
    @Override
    public CreditGroup getPreviousCreditGroupWs(CreditGroup cg){
        
        double upgradeLimit = cg.getUpgradeLimitForWs();
        Query q = entityManager.createQuery("SELECT c FROM CreditGroup c WHERE c.upgradeLimitForWs <:p1 ORDER BY c.upgradeLimitForWs DESC");
        q.setParameter("p1", upgradeLimit);
        ArrayList<CreditGroup> list = new ArrayList<CreditGroup>(q.getResultList());
        if(list.isEmpty()){
           return null; 
        }
        CreditGroup prev = list.get(0);
        System.out.println("Prev WS: " + prev.getCreditGroupTitle() + " " + prev.getCreditLimitForWs());
        
        return prev;
    }
    
    @Override
    public CreditGroup getNextCreditGroupWs(CreditGroup cg){
        
        double upgradeLimit = cg.getUpgradeLimitForWs();
        Query q = entityManager.createQuery("SELECT c FROM CreditGroup c WHERE c.upgradeLimitForWs >:p1 ORDER BY c.upgradeLimitForWs ASC");
        q.setParameter("p1", upgradeLimit);
        ArrayList<CreditGroup> list = new ArrayList<CreditGroup>(q.getResultList());
        if(list.isEmpty()){
           return null; 
        }
        CreditGroup next = list.get(0);
        System.out.println("Next WS: " + next.getCreditGroupTitle() + " " + next.getCreditLimitForWs());
        
        return next;
    }
     @Override
    public Customer getCustomer(Long id) {
         Query q = entityManager.createQuery("SELECT c FROM Customer c WHERE c.id =:p1");
         q.setParameter("p1", id);
         return (Customer)q.getSingleResult();
    }

}
